package com.jet.chat.service;

//import java.io.Reader;
//import java.io.StringReader;
import java.sql.Connection;
//import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
//import java.util.ArrayList;
//import java.util.Properties;

//import com.jet.chat.exception.ChatExceptionCheckAuthSQLException;

public class DBQueryService {
	public static void execUPD(DBConnectionService dbService, String sql) {
		try {
			if (dbService.isConnected()) {
				Connection conn = dbService.getConn();
				Statement stmt = conn.createStatement();
				stmt.execute(sql);
				stmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
	//[ek] TODO low cohesion detected. Two different responsibilities
	public static void execSQL(DBConnectionService dbService, String sql) {
		try {
			if (dbService.isConnected()) {
				Connection conn = dbService.getConn();
				Statement stmt = conn.createStatement();
				ResultSet results = stmt.executeQuery(sql);
				ResultSetMetaData rsmd = results.getMetaData();
				int numberCols = rsmd.getColumnCount();

				String resultString = "";

				for (int i=1; i<=numberCols; i++) {
					resultString = resultString + rsmd.getColumnLabel(i) + "\t\t";
				}

				System.out.println(resultString);
				resultString = "";

				while(results.next()) {
					for (int i=1; i<=numberCols; i++) {
						resultString = resultString + results.getString(i) + "\t\t";
					}
					
					System.out.println(resultString);
					resultString = "";
				}
				results.close();
				stmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
//	public static synchronized void addHistory(DBConnectionService dbService, int senderId, int recieverId, String channelName, String content) {
//		try {
//			Reader reader = new StringReader(content);
//			if (content == null) {
//			} else {
//				if (dbService.isConnected()) {
//					Connection conn = dbService.getConn();
//					Statement stmt = conn.createStatement();
//					ResultSet results = stmt.executeQuery("select max(id) from history");
//					int id = 0;
//					while(results.next()) {
//						id = results.getInt(1);
//					}
//					id = id + 1;
//					results.close();
//					stmt.close();
//					
//					PreparedStatement ps = conn.prepareStatement("insert into history (id, senderid, receiverid, channelname, content) values (?, ?, ?, ?, ?)");
//					ps.setInt(1, id);
//					ps.setInt(2, senderId);
//					ps.setInt(3, recieverId);
//					ps.setString(4, channelName);
//					ps.setClob(5, reader);
//					ps.execute();
//					conn.commit();
//				}
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//
//	}
	
//	public static synchronized ArrayList<Properties> getChannelHistory(DBConnectionService dbService, String ChannelName) {
//		ArrayList<Properties> ret = new ArrayList<Properties>();
//		try {
//			if (dbService.isConnected()) {
//				Connection conn = dbService.getConn();
//				PreparedStatement ps;
//				ps = conn.prepareStatement("select u.username, h.content from history as h, users as u where h.channelname = ? and u.id = h.senderid and h.receiverid = ?");
//				ps.setString(1, ChannelName);
//				ps.setInt(2, 0);
//				ResultSet results = ps.executeQuery();
//				while(results.next()) {
//					Properties raw = new Properties();
//					raw.put("username", results.getString(1));
//					raw.put("content", results.getString(2));
//					ret.add(raw);
//				}
//				results.close();
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//
//		return ret;
//	}
	
//	public static synchronized ArrayList<Properties> getWhisperHistory(DBConnectionService dbService, int userId) {
//		ArrayList<Properties> ret = new ArrayList<Properties>();
//		try {
//			if (dbService.isConnected()) {
//				Connection conn = dbService.getConn();
//				PreparedStatement ps;
//				ps = conn.prepareStatement("select u.username, h.content from history as h, users as u where h.receiverid = ? and u.id = h.senderid");
//				ps.setInt(1, userId);
//				ResultSet results = ps.executeQuery();
//				while(results.next()) {
//					Properties raw = new Properties();
//					raw.put("username", results.getString(1));
//					raw.put("content", results.getString(2));
//					ret.add(raw);
//				}
//				results.close();
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//
//		return ret;
//	}
	
//	public static synchronized ArrayList<Properties> getHistory(DBConnectionService dbService, int userId) {
//		ArrayList<Properties> ret = new ArrayList<Properties>();
//		try {
//			if (dbService.isConnected()) {
//				Connection conn = dbService.getConn();
//				PreparedStatement ps;
//				ps = conn.prepareStatement("select u.username, h.receiverid, h.channelname, h.content from history as h, users as u where (h.receiverid = ? or h.receiverid = ?) and u.id = h.senderid and h.channelname in (select channelname from history where senderid = ? group by channelname)");
//				ps.setInt(1, userId);
//				ps.setInt(2, 0);
//				ps.setInt(3, userId);
//				ResultSet results = ps.executeQuery();
//				while(results.next()) {
//					Properties raw = new Properties();
//					raw.put("username", results.getString(1));
//					raw.put("receiverid", results.getInt(2));
//					raw.put("channelname", results.getString(3));
//					raw.put("content", results.getString(4));
//					ret.add(raw);
//				}
//				results.close();
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return ret;
//	}
	
//	public static int checkAuth(DBConnectionService dbService, String username, String password) throws ChatExceptionCheckAuthSQLException {
//		int userId = 0;
//		PreparedStatement ps;
//		try {
//			if (dbService.isConnected()) {
//				Connection conn = dbService.getConn();
//				ps = conn.prepareStatement("select id from users where username = ? and password = ?");
//				ps.setString(1, username);
//				ps.setString(2, password);
//				ResultSet results = ps.executeQuery();
//				while(results.next()) {
//					userId = results.getInt(1);
//				}
//				results.close();
//			}
//		} catch (SQLException e) {
//			throw new ChatExceptionCheckAuthSQLException("ошибка при авторизации");
//		}
//
//		return userId;
//	}
}
